INSERT INTO FOTO_PRODUCTO
 SELECT PRODUCTO.ID, NULL, NULL FROM PRODUCTO

 INSERT INTO FOTO_TRABAJADOR
 VALUES (1, NULL, NULL )


CREATE PROCEDURE [dbo].[GetGuiaEntrada]
@IdGuia int
AS
BEGIN
SELECT     P.RazonSocial, P.Ruc, P.Direccion, P.Telefono,
		   G.Fecha, G.Total, g.NroDoc,
		   ROW_NUMBER() OVER(ORDER BY DG.Id DESC) AS Item,
		   PRO.Nombre AS PRODUCTOs, UM.Descripcion UndmEDIDAS,
		   DG.Cantidad, DG.ValorUnitario,(DG.Cantidad * DG.ValorUnitario) TotalItem
		   
FROM 
UNIDAD_MEDIDA UM INNER JOIN PRODUCTO PRO ON UM.Id = PRO.IdUnidadMedida
INNER JOIN DETALLE_GUIA_ENTRADA DG ON PRO.Id = DG.IdProducto
INNER JOIN GUIA_ENTRADA G ON DG.IdGuiaEntrada = G.Id 
INNER JOIN PROVEEDOR P ON G.IdProveedor = P.Id
WHERE P.Id = @IdGuia
END


CREATE PROCEDURE [dbo].[GetPecosa]
@IdPecosa int
AS
BEGIN
SELECT     (T.Nombres + ' ' + T.ApePaterno + ' ' + T.ApeMaterno)  RazonSocial, T.Dni,
		   PEC.Fecha, PEC.Total, PEC.NroDoc,
		   ROW_NUMBER() OVER(ORDER BY Dp.Id DESC) AS Item,
		   PRO.Nombre AS PRODUCTOs, UM.Descripcion UndmEDIDAS,
		   DP.Cantidad, DP.ValorUnitario,(DP.Cantidad * DP.ValorUnitario) TotalItem
		   
FROM 
UNIDAD_MEDIDA UM INNER JOIN PRODUCTO PRO ON UM.Id = PRO.IdUnidadMedida
INNER JOIN DETALLE_PECOSA DP ON PRO.Id = DP.IdProducto
INNER JOIN PECOSA PEC ON DP.IdPecosa = PEC.Id 
INNER JOIN TRABAJADOR T ON PEC.IdSolicitante = T.Id
WHERE PEC.Id = @IdPecosa
END


CREATE PROCEDURE [dbo].[ASIGNAR_ROL_USUARIO]
@UserName varchar(8),
@RoleName varchar(20)
AS
declare
	@UserId int,
	@RoleId int
begin				


		SELECT  @UserId =  U.UserId
		FROM dbo.UserProfile U  
		where U.UserName = @UserName
		
		SELECT  @RoleId =  R.RoleId
		FROM dbo.webpages_Roles R 
		where R.RoleName = @RoleName 
		
		IF @UserId != 1
		begin
		DELETE FROM dbo.webpages_UsersInRoles WHERE UserId = @UserId
		INSERT INTO dbo.webpages_UsersInRoles VALUES(@UserId,@RoleId)		
		end
end
/******************/


CREATE PROCEDURE [dbo].[GastosPartidas]
AS
BEGIN
SELECT CC.Descripcion as Partida,
 sum(CASE WHEN month(RP.Fecha) = 1 THEN RP.Monto ELSE 0 END) AS Ene,
 sum(CASE WHEN month(RP.Fecha) = 2 THEN RP.Monto ELSE 0 END) AS Feb,
 sum(CASE WHEN month(RP.Fecha) = 3 THEN RP.Monto ELSE 0 END) AS Mar,
 sum(CASE WHEN month(RP.Fecha) = 4 THEN RP.Monto ELSE 0 END) AS Abr,
 sum(CASE WHEN month(RP.Fecha) = 5 THEN RP.Monto ELSE 0 END) AS May,
 sum(CASE WHEN month(RP.Fecha) = 6 THEN RP.Monto ELSE 0 END) AS Jun,
 sum(CASE WHEN month(RP.Fecha) = 7 THEN RP.Monto ELSE 0 END) AS Jul,
 sum(CASE WHEN month(RP.Fecha) = 8 THEN RP.Monto ELSE 0 END) AS Ago,
 sum(CASE WHEN month(RP.Fecha) = 9 THEN RP.Monto ELSE 0 END) AS Sep,
 sum(CASE WHEN month(RP.Fecha) = 10 THEN RP.Monto ELSE 0 END) AS Oct,
 sum(CASE WHEN month(RP.Fecha) = 11 THEN RP.Monto ELSE 0 END) AS Nov,
 sum(CASE WHEN month(RP.Fecha) = 12 THEN RP.Monto ELSE 0 END) AS Dic,
 sum(RP.Monto) AS Total
from REDUCIR_PRESUPUESTO RP inner join PARTIDA P on RP.IdPartida = P.Id
INNER JOIN DETALLE_PARTIDA DP ON P.Id = DP.IdPartida
INNER JOIN FUENTE_FINANCIERA FF ON DP.IdFuenteFinanciera = FF.Id
INNER JOIN CENTRO_COSTO CC ON FF.IdCentroCosto = CC.ID
where YEAR(RP.Fecha) = YEAR(GETDATE())
group by CC.Descripcion      
END


CREATE PROCEDURE [dbo].[GastosPorCentro]
@IdCentro int
AS
BEGIN
SELECT CC.Descripcion as CentroCosto, P.Codigo, P.Nombre,
 sum(CASE WHEN month(RP.Fecha) = 1 THEN RP.Monto ELSE 0 END) AS Ene,
 sum(CASE WHEN month(RP.Fecha) = 2 THEN RP.Monto ELSE 0 END) AS Feb,
 sum(CASE WHEN month(RP.Fecha) = 3 THEN RP.Monto ELSE 0 END) AS Mar,
 sum(CASE WHEN month(RP.Fecha) = 4 THEN RP.Monto ELSE 0 END) AS Abr,
 sum(CASE WHEN month(RP.Fecha) = 5 THEN RP.Monto ELSE 0 END) AS May,
 sum(CASE WHEN month(RP.Fecha) = 6 THEN RP.Monto ELSE 0 END) AS Jun,
 sum(CASE WHEN month(RP.Fecha) = 7 THEN RP.Monto ELSE 0 END) AS Jul,
 sum(CASE WHEN month(RP.Fecha) = 8 THEN RP.Monto ELSE 0 END) AS Ago,
 sum(CASE WHEN month(RP.Fecha) = 9 THEN RP.Monto ELSE 0 END) AS Sep,
 sum(CASE WHEN month(RP.Fecha) = 10 THEN RP.Monto ELSE 0 END) AS Oct,
 sum(CASE WHEN month(RP.Fecha) = 11 THEN RP.Monto ELSE 0 END) AS Nov,
 sum(CASE WHEN month(RP.Fecha) = 12 THEN RP.Monto ELSE 0 END) AS Dic,
 sum(RP.Monto) AS Total
from REDUCIR_PRESUPUESTO RP inner join PARTIDA P on RP.IdPartida = P.Id
INNER JOIN DETALLE_PARTIDA DP ON P.Id = DP.IdPartida
INNER JOIN FUENTE_FINANCIERA FF ON DP.IdFuenteFinanciera = FF.Id
INNER JOIN CENTRO_COSTO CC ON FF.IdCentroCosto = CC.ID
where YEAR(RP.Fecha) = YEAR(GETDATE()) and CC.ID = @IdCentro
group by CC.Descripcion, P.Codigo, P.Nombre
END